1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmCurrentStock
6
7 Public Sub Getdata()
8 Try
9 con = New SqlConnection(cs)
10 con.Open()
11 cmd = New SqlCommand("SELECT PID, RTRIM(Product.ProductCode),RTRIM(ProductName),RTRIM(Description),(CostPrice),(SellingPrice),(Discount),(VAT),Qty from Temp_Stock,Product where Product.PID=Temp_Stock.ProductID and Qty > 0 order by ProductName", con)
12 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
13 dgw.Rows.Clear()
14 While (rdr.Read() = True)
15 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
16 End While
17 con.Close()
18 Catch ex As Exception
19 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
20 End Try
21 End Sub
22 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
23 Getdata()
24
25 dgw.Columns(4).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
26 dgw.Columns(5).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
27 dgw.Columns(6).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
28 dgw.Columns(7).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
29 dgw.Columns(8).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight
30 End Sub
31
32 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
33 Try
34 If dgw.Rows.Count > 0 Then
35 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
36 If lblSet.Text = "Billing" Then
37 frmBilling.Show()
38 Me.Hide()
39 frmBilling.txtProductID.Text = dr.Cells(0).Value.ToString()
40 frmBilling.txtProductCode.Text = dr.Cells(1).Value.ToString()
41 frmBilling.txtProductName.Text = dr.Cells(2).Value.ToString()
42 frmBilling.txtCostPrice.Text = dr.Cells(4).Value.ToString()
43 frmBilling.txtSellingPrice.Text = dr.Cells(5).Value.ToString()
44 Dim num As Double
45 num = Val(dr.Cells(5).Value) - Val(dr.Cells(4).Value)
46 num = Math.Round(num, 2)
47 frmBilling.txtMargin.Text = num
48 frmBilling.txtDiscountPer.Text = dr.Cells(6).Value.ToString()
49 frmBilling.txtVAT.Text = dr.Cells(7).Value.ToString()
50 frmBilling.txtQty.Focus()
51 lblSet.Text = ""
52 End If
53 If lblSet.Text = "Billing1" Then
54 frmBilling1.Show()
55 Me.Hide()
56 frmBilling1.txtProductID.Text = dr.Cells(0).Value.ToString()
57 frmBilling1.txtProductCode.Text = dr.Cells(1).Value.ToString()
58 frmBilling1.txtProductName.Text = dr.Cells(2).Value.ToString()
59 frmBilling1.txtCostPrice.Text = dr.Cells(4).Value.ToString()
60 frmBilling1.txtSellingPrice.Text = dr.Cells(5).Value.ToString()
61 Dim num As Double
62 num = Val(dr.Cells(5).Value) - Val(dr.Cells(4).Value)
63 num = Math.Round(num, 2)
64 frmBilling1.txtMargin.Text = num
65 frmBilling1.txtDiscountPer.Text = dr.Cells(6).Value.ToString()
66 frmBilling1.txtVAT.Text = dr.Cells(7).Value.ToString()
67 frmBilling1.txtQty.Focus()
68 lblSet.Text = ""
69 End If
70 End If
71 Catch ex As Exception
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
73 End Try
74 End Sub
75
76 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
77 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
78 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
79 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
80 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
81 End If
82 Dim b As Brush = SystemBrushes.ControlText
83 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
84
85 End Sub
86 Sub Reset()
87 txtProductName.Text = ""
88 Getdata()
89 End Sub
90 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
91 Reset()
92 End Sub
93
94 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
95 Me.Close()
96 End Sub
97
98
99
100 Private Sub btnExportExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnExportExcel.Click
101 Dim rowsTotal, colsTotal As Short
102 Dim I, j, iC As Short
103 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
104 Dim xlApp As New Excel.Application
105 Try
106 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
107 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
108 xlApp.Visible = True
109
110 rowsTotal = dgw.RowCount
111 colsTotal = dgw.Columns.Count - 1
112 With excelWorksheet
113 .Cells.Select()
114 .Cells.Delete()
115 For iC = 0 To colsTotal
116 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
117 Next
118 For I = 0 To rowsTotal - 1
119 For j = 0 To colsTotal
120 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
121 Next j
122 Next I
123 .Rows("1:1").Font.FontStyle = "Bold"
124 .Rows("1:1").Font.Size = 12
125
126 .Cells.Columns.AutoFit()
127 .Cells.Select()
128 .Cells.EntireColumn.AutoFit()
129 .Cells(1, 1).Select()
130 End With
131 Catch ex As Exception
132 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
133 Finally
134 'RELEASE ALLOACTED RESOURCES
135 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
136 xlApp = Nothing
137 End Try
138 End Sub
139
140 Private Sub txtProductName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtProductName.TextChanged
141 Try
142 con = New SqlConnection(cs)
143 con.Open()
144 cmd = New SqlCommand("SELECT PID, RTRIM(Product.ProductCode),RTRIM(ProductName),RTRIM(Description),(CostPrice),(SellingPrice),(Discount),(VAT),Qty from Temp_Stock,Product where Product.PID=Temp_Stock.ProductID and Qty > 0 and ProductName like '%" & txtProductName.Text & "%' order by ProductName", con)
145 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
146 dgw.Rows.Clear()
147 While (rdr.Read() = True)
148 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8))
149 End While
150 con.Close()
151 Catch ex As Exception
152 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
153 End Try
154 End Sub
155 End Class
